package com.wcgksoft.service;

import com.alibaba.fastjson.JSONArray;
import com.wcgksoft.common.base.BaseService;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class HomePageService extends BaseService {
   public JSONArray HomePage (String  departid, String projectid){
     JSONArray objects = new JSONArray();
     StringBuilder stringBuilder = new StringBuilder();
     StringBuilder upyh = new StringBuilder();
     StringBuilder byxc = new StringBuilder();
     StringBuilder upxc = new StringBuilder();
      //
     stringBuilder.append("select count(*) as plantt from t_Daily_Maintenance where ");
     stringBuilder.append("departid='"+departid+"' and  ");
     stringBuilder.append("xiangmuid= '"+projectid+"' ");
       stringBuilder.append("and DATE_FORMAT(releasetime, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )");
      //

     upyh.append("SELECT count(*) as mmmmmma FROM t_Daily_Maintenance WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( releasetime, '%Y%m' ) ) =1 and  ");
     upyh.append("departid='"+departid+"' and xiangmuid='"+projectid+"' ");
      //
     byxc.append("select count(*) as patrodd from t_patrolaccount where ");
     byxc.append("departid='"+departid+"' and status like '1' and xiangmuid='"+projectid+"' ");
     byxc.append("and DATE_FORMAT(releasetime, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )");
      //
     upxc.append("SELECT count(*) as mmmmmmbb FROM t_patrolaccount WHERE ");
     upxc.append("PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( releasetime, '%Y%m' ) ) =1 and departid='"+departid+"' and status like '1' and xiangmuid='"+projectid+"' ");

     List<Integer> sqlQuery = (List<Integer>) this.getHibernateSession().createSQLQuery(stringBuilder.toString());
     List<Integer> upyhc = (List<Integer>) this.getHibernateSession().createQuery(upyh.toString());
     List<Integer> byxcc = (List<Integer>) this.getHibernateSession().createQuery(byxc.toString());
     List<Integer> upxcc = (List<Integer>) this.getHibernateSession().createQuery(upxc.toString());
    // List<Integer> bybclist = this.createSqlQuery("select count(*) as ressdddddd from t_insectdetection where departid='"+departid+"' and xiangmuid='"+projectid+"' and DATE_FORMAT(releasetime, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m') ");
     List<Integer> bybclist = (List<Integer>) this.getHibernateSession().createQuery("select count(*) as ressdddddd from t_insectHistory where departid='"+departid+"' and xiangmuid='"+projectid+"' and DATE_FORMAT(releasetime, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m')");
     List<Integer> sybclist = (List<Integer>) this.getHibernateSession().createQuery(" SELECT count(*) as mmmmmmcc FROM t_insectHistory WHERE  departid='"+departid+"' and xiangmuid='"+projectid+"' and PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( releasetime, '%Y%m' ) ) =1 ");
      //  List<Integer> byydlist = this.createSqlQuery(" SELECT count(*) as mmmmmmcc FROM t_insectdetection WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( time, '%Y%m' ) ) =1 and departid="+departid+" and xiangmuid="+projectid+" ");

     objects.add(sqlQuery.get(0));// 本月养护 [0]
     objects.add(upyhc.get(0));// 上月养护 [1]
     objects.add(byxcc.get(0));// 本月巡查 [2]
     objects.add(upxcc.get(0));// 上月巡查 [3]
     objects.add(bybclist.get(0));// 本月病虫 [4]
     objects.add(sybclist.get(0));// 上月病虫 [5]
     return objects;
   }


}
